package com.cennavi.bigdata.baotou.dao.impl;

import com.cennavi.bigdata.baotou.dao.EmergencyDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * Created by cennavi on 2018/10/17.
 */
@Repository
public class EmergencyDaoImpl implements EmergencyDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Map<String, Object>> listEmergencyRoll(String dateStr, int count) {
        String  sql = "SELECT * from (SELECT A .JQBH as id, to_char( A .BJSJ,'yyyy-mm-dd hh24:mi:ss') as time, A .JQZLX as type, b.DMSM1 as typename, A .LCZT as handletype, A .YLZD9 as influencetype, A.BJNR as detail FROM ERG_ALARM A, FRM_CODE b WHERE A .JQZLX = b.DMZ AND A .BJSJ <= \"TO_DATE\" ( '"+dateStr+"', 'yyyy-mm-dd HH24:mi:ss' ) AND A.JQLX='07' AND A .JQZLX LIKE '07%' ORDER BY A .BJSJ DESC ) s WHERE ROWNUM <="+count;
        System.out.println(sql);
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> listEmergency(String startTime, String endTime, String type, String handletype, String influencetype, int count) {
        String sql;
        if (count==0){
            sql = " SELECT A .JQBH AS ID, to_char( A .BJSJ,'yyyy-mm-dd hh24:mi:ss') AS TIME, A .JQZLX AS TYPE, b.DMSM1 AS typename, A .LCZT AS handletype, A .YLZD9 AS influencetype, A .BJNR AS detail FROM ERG_ALARM A, FRM_CODE b WHERE A .JQZLX = b.DMZ AND A .BJSJ >= \"TO_DATE\" ( '"+startTime+"', 'yyyy-mm-dd HH24:mi:ss' ) AND A .BJSJ <= \"TO_DATE\" ( '"+endTime+"', 'yyyy-mm-dd HH24:mi:ss' ) AND A.JQLX='07' " ;
            if (!type.equals("0")){
                sql += " AND A.JQZLX = '"+type+"'";
            }else {
                sql += " AND A.JQZLX LIKE '07%'";
            }
            if (!handletype.equals("0")){
                sql += "AND A .LCZT = '"+handletype+"'";
            }
            if (!influencetype.equals("0")){
                sql += "  AND A .YLZD9 ='"+influencetype+"'";
            }
            sql += " ORDER BY A .BJSJ DESC";
        }else {
            sql = "SELECT * FROM ( SELECT A .JQBH AS ID, to_char( A .BJSJ,'yyyy-mm-dd hh24:mi:ss') AS TIME, A .JQZLX AS TYPE, b.DMSM1 AS typename, A .LCZT AS handletype, A .YLZD9 AS influencetype, A .BJNR AS detail FROM ERG_ALARM A, FRM_CODE b WHERE A .JQZLX = b.DMZ AND A .BJSJ >= \"TO_DATE\" ( '"+startTime+"', 'yyyy-mm-dd HH24:mi:ss' ) AND A .BJSJ <= \"TO_DATE\" ( '"+endTime+"', 'yyyy-mm-dd HH24:mi:ss' ) AND A.JQLX='07' ";
            if (!type.equals("0")){
                sql += "AND A .JQZLX = '"+type+"'";
            }else {
                sql += " AND A.JQZLX LIKE '07%'";
            }
            if (!handletype.equals("0")){
                sql += "AND A .LCZT = '"+handletype+"'";
            }
            if (!influencetype.equals("0")){
                sql += "  AND A .YLZD9 ='"+influencetype+"'";
            }
            sql += "ORDER BY A .BJSJ DESC ) s WHERE ROWNUM <= "+count;
        }
        System.out.println(sql);
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> listEmergencyCountByType(String startTime, String endTime, String type, String handletype, String influencetype) {
        String sql = "SELECT A .JQZLX AS TYPE, b.DMSM1 AS typename, COUNT (1) AS COUNT FROM ERG_ALARM A, FRM_CODE b WHERE A .JQZLX = b.DMZ AND A .BJSJ >= \"TO_DATE\" ( '"+startTime+"', 'yyyy-mm-dd HH24:mi:ss' ) AND A .BJSJ <= \"TO_DATE\" ( '"+endTime+"', 'yyyy-mm-dd HH24:mi:ss' ) AND A.JQLX='07'  ";
        if (!type.equals("0")){
            sql += "AND A .JQZLX = '"+type+"'";
        }else {
            sql += " AND A.JQZLX LIKE '07%'";
        }
        if (!handletype.equals("0")){
            sql += " AND A .LCZT = '"+handletype+"'";
        }
        if (!influencetype.equals("0")){
            sql += "  AND A .YLZD9 ='"+influencetype+"'";
        }
        sql += " AND A .JQZLX IS NOT NULL GROUP BY A .JQZLX, b.DMSM1 ORDER  BY A .JQZLX";
        System.out.println(sql);
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> listEmergencyCountByHandletype(String startTime, String endTime, String type, String handletype, String influencetype) {
        String sql = "SELECT A .LCZT AS TYPE , COUNT (1) AS COUNT FROM ERG_ALARM A WHERE A .BJSJ >= \"TO_DATE\" ( '"+startTime+"', 'yyyy-mm-dd HH24:mi:ss' ) AND A .BJSJ <= \"TO_DATE\" ( '"+endTime+"', 'yyyy-mm-dd HH24:mi:ss' ) AND A.JQLX='07' ";
        if (!type.equals("0")){
            sql += "AND A .JQZLX = '"+type+"'";
        }else {
            sql += " AND A.JQZLX LIKE '07%'";
        }
        if (!handletype.equals("0")){
            sql += " AND A .LCZT = '"+handletype+"'";
        }
        if (!influencetype.equals("0")){
            sql += "  AND A .YLZD9 ='"+influencetype+"'";
        }
        sql += " AND A .LCZT is NOT NULL GROUP BY A.LCZT ORDER BY a.LCZT";
        System.out.println(sql);
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> listEmergencyCountByInfluencetype(String startTime, String endTime, String type, String handletype, String influencetype) {
        String sql = "SELECT A.ylzd9 AS TYPE , COUNT (1) AS COUNT FROM ERG_ALARM A WHERE A .BJSJ >= \"TO_DATE\" ( '"+startTime+"', 'yyyy-mm-dd HH24:mi:ss' ) AND A .BJSJ <= \"TO_DATE\" ( '"+endTime+"', 'yyyy-mm-dd HH24:mi:ss' ) AND A.JQLX='07' ";
        if (!type.equals("0")){
            sql += "AND A .JQZLX = '"+type+"'";
        }else {
            sql += " AND A.JQZLX LIKE '07%' ";
        }
        if (!handletype.equals("0")){
            sql += " AND A .LCZT = '"+handletype+"'";
        }
        if (!influencetype.equals("0")){
            sql += "  AND A .YLZD9 ='"+influencetype+"'";
        }
        sql += " AND a.ylzd9 is NOT NULL GROUP BY A.ylzd9 ORDER BY a.ylzd9";
        System.out.println(sql);
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> listType() {
        String  sql = "SELECT a.DMZ as TYPE ,a.DMSM1 as TYPENAME from FRM_CODE a WHERE a.XTLB='74' AND a.DMLB='0009' AND a.DMSM2='07' ORDER BY a.DMZ";
        System.out.println(sql);
        return jdbcTemplate.queryForList(sql);
    }
}
